package com.ifnk.portaljava.hlyt.hsqldb;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.*;
import java.util.Iterator;


public class HsqlDaoImpl implements HsqlDao{
	private static String url = "jdbc:hsqldb:mem:portaldb";
	public void creatTable(String sql, Connection con) throws Exception {
		Statement st = null;;
		try {
			st = con.createStatement();
			st.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("create table error");
		}finally {
			if(st != null) {
				st.close();
			}
		}
	}
	public Connection getConnection() throws Exception {
		Connection con = null;
		try {
			Class.forName("org.hsqldb.jdbcDriver");
			con = DriverManager.getConnection(url, "sa", "");
		} catch (Exception e) {
			throw new Exception("getConnection error");
		}
		return con;
	}
	public void delete(Connection con) throws Exception {
		String sql = "delete from cdrList";
		Statement st = null;
		try{
			st = con.createStatement();
			st.execute(sql);
		}catch(Exception e) {
			e.printStackTrace();
			throw new Exception("delete Hsqldb error");
		}finally {
			if(st != null) {
				st.close();
			}
		}
		
	}
	public void save(String sql, Connection con) throws Exception {
		Statement st = null;
		try{
			st = con.createStatement();
			st.execute(sql);
		}catch(Exception e) {
			e.printStackTrace();
			throw new Exception("save error");
		}finally {
			if(st != null) {
				st.close();
			}
		}
	}
	public void find() throws Exception {
		Statement st = null;
		ResultSet rs = null;
		try{
			String sql = "select _Sync,_Type from cdrList";
			st = this.getConnection().createStatement();
			rs = st.executeQuery(sql);
			 while(rs.next()){
	    		 System.out.println("_Sync: "+rs.getString("_Sync")+" , " + rs.getString("_Type"));
	    	 }
		}catch(Exception e) {
			e.printStackTrace();
			throw new Exception("save error");
		}finally {
			if(rs != null) {
				rs.close();
			}
			if(st != null) {
				st.close();
			}
		}
	}
	public void dropTable() throws Exception {
		Connection con = null;
		Statement st  = null;
		try{
			con = getConnection();
			String sql = "DROP TABLE IF EXISTS cdrList";
			st = con.createStatement();
			st.execute(sql);
			con.commit();
		}catch(Exception e) {
			con.rollback();
			e.printStackTrace();
			throw new Exception("drop table error");
		}finally {
			if(st != null) {
				st.close();
			}
			if(con != null) {
				con.close();
			}
		}
	}
	public String parseXMLToSql() {
		SAXReader reader = new SAXReader();
		StringBuffer column = new StringBuffer();
		StringBuffer values = new StringBuffer();
		String sql = "";
		try {
			Document document =reader.read(new FileInputStream(new File("C:\\Users\\s\\Desktop\\allCDR.xml")));
			Element element = document.getRootElement();
			Iterator it = element.elements().iterator();
			for(Iterator its = it ; its.hasNext() ;) {
				Element el = (Element)its.next();
				if(column.length() <=0) {
					column.append("_" + el.getName());
				} else {
					column.append(",_" + el.getName());
				}
				if(values.length() <=0) {
					values.append("'" + el.getText() + "'");
				} else {
					values.append(",'" + el.getText() + "'");
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (DocumentException e) {
			e.printStackTrace();
		}
		 sql = "insert into cdrList (" + column + ") values (" + values +")";
		 return sql;
	}
	public void update(Connection con) throws Exception {
		Statement st = null;
		String sql = "";
		try{
			st = con.createStatement();
			st.execute(sql);
		}catch(Exception e) {
			e.printStackTrace();
			throw new Exception("save error");
		}finally {
			if(st != null) {
				st.close();
			}
		}
	}
}
